package com.etone.smartAudit.utils.Excel;

import com.etone.smartAudit.domain.IapDataMeta;
import com.etone.smartAudit.domain.IapDataModel;
import com.etone.smartAudit.enums.UpdateFrequency;
import com.etone.smartAudit.enums.UseState;
import com.etone.smartAudit.error.CustomParameterizedException;
import lombok.Data;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author: 蒋学伟
 * @create: 2020-08-31 16:40:22
 * @Description ExcelUtil
 */
@Component
public class IapDataModelExcelUtil {

    public static String xls = ".xls";
    public static String xlsx = ".xlsx";

    public static String[] iapDataModelHead = {"宽表表名", "英文表名", "系统名称", "更新频率", "保存周期", "状态", "一级分类", "二级分类", "说明", "敏感性", "数据存放平台", "数据维护类型", "数据质量纬度", "使用方纬度", "自动化纬度", "数据时效性开始时间", "数据时效性结束时间"};

    public static String[] iapDataMetaHead = {"字段", "类型", "默认值", "说明", "是否主键", "是否外键", "外键说明", "是否分区字段", "是否唯一约束", "是否允许空值"};


    /**
     * 读取excel,只读取第一个sheel
     *
     * @param file             excel文件
     * @param importErrorInfos 错误信息内容
     * @return
     */
    public static List<IapDataModel> read(MultipartFile file, List<ImportErrorInfo> importErrorInfos) {
        Workbook workbook = getWorkbook(file);

        //获取元数据
        List<IapDataModel> iapDataModes = getIapDataMode(workbook.getSheetAt(0), importErrorInfos);

        //获取数据字典
        List<IapDataMeta> iapDataMetas = getIapDataMeta(workbook.getSheetAt(1), iapDataModes, importErrorInfos);

        //校验重复内容
        checkErrorMsg(iapDataModes, iapDataMetas, importErrorInfos);

        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return iapDataModes;
    }


    /**
     * 获取元数据
     *
     * @param sheet
     * @param importErrorInfos
     * @return
     */
    public static List<IapDataModel> getIapDataMode(Sheet sheet, List<ImportErrorInfo> importErrorInfos) {

        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum >= 0) {
            List<IapDataModel> iapDataModels = new ArrayList<>();
            Row headRow = sheet.getRow(0);
            //表头所在列
            Map<String, Integer> headIndexMap = getHeadIndex(headRow, iapDataModelHead);
            Date now = new Date();
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);

                String headName = "宽表表名";
                Integer cellIndex = headIndexMap.get(headName);
                String cnTableName = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(cnTableName)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "英文表名";
                cellIndex = headIndexMap.get(headName);
                String enTableName = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(enTableName)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "系统名称";
                cellIndex = headIndexMap.get(headName);
                String platformName = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(platformName)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "更新频率";
                cellIndex = headIndexMap.get(headName);
                String updateFreqStr = getStringValue(row, cellIndex);
                UpdateFrequency updateFreq = UpdateFrequency.getByValue(updateFreqStr);
                if (updateFreq == null && !StringUtils.isEmpty(updateFreqStr)) {
                    importErrorInfos.add(ImportErrorInfo.formatError(i + 1, cellIndex, headName));
                }

                headName = "保存周期";
                cellIndex = headIndexMap.get(headName);
                Integer psvCycle = getIntegerValue(row, cellIndex);

                headName = "状态";
                cellIndex = headIndexMap.get(headName);
                String useStateStr = getStringValue(row, cellIndex);
                UseState useState = UseState.getByValue(useStateStr);
                if (useState == null && !StringUtils.isEmpty(useStateStr)) {
                    importErrorInfos.add(ImportErrorInfo.formatError(i + 1, cellIndex, headName));
                }

                headName = "一级分类";
                cellIndex = headIndexMap.get(headName);
                String level1 = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(level1)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "二级分类";
                cellIndex = headIndexMap.get(headName);
                String level2 = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(level2)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

//                headName = "三级分类";
//                cellIndex = headIndexMap.get(headName);
//                String level3 = getStringValue(row, cellIndex);
//                if (StringUtils.isEmpty(level3)) {
//                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
//                }

                headName = "说明";
                cellIndex = headIndexMap.get(headName);
                String description = getStringValue(row, cellIndex);

                headName = "敏感性";
                cellIndex = headIndexMap.get(headName);
                String senLevel = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(senLevel)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "数据存放平台";
                cellIndex = headIndexMap.get(headName);
                String dataPssA = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(dataPssA)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "数据维护类型";
                cellIndex = headIndexMap.get(headName);
                String dataAssertNames = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(dataAssertNames)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "数据质量纬度";
                cellIndex = headIndexMap.get(headName);
                String dataMassNames = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(dataMassNames)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "使用方纬度";
                cellIndex = headIndexMap.get(headName);
                String dimensionNames = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(dimensionNames)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "自动化纬度";
                cellIndex = headIndexMap.get(headName);
                String autoTagNames = getStringValue(row, cellIndex);
                if (StringUtils.isEmpty(autoTagNames)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "数据时效性开始时间";
                cellIndex = headIndexMap.get(headName);
                Date startDateTime = getDataValue(row,cellIndex);
                if (StringUtils.isEmpty(startDateTime)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                headName = "数据时效性结束时间";
                cellIndex = headIndexMap.get(headName);
                Date endDateTime = getDataValue(row, cellIndex);
                if (StringUtils.isEmpty(endDateTime)) {
                    importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                }

                iapDataModels.add(
                        IapDataModel
                                .builder()
                                .cnTableName(cnTableName)
                                .enTableName(enTableName)
                                .platformName(platformName)
                                .updateFreq(updateFreq)
                                .psvCycle(psvCycle)
                                .useState(useState)
                                .level1(level1)
                                .level2(level2)
//                                .level3(level3)
                                .description(description)
                                .senLevel(senLevel)
                                .dataPssA(dataPssA)
                                .dataAssertNames(dataAssertNames)
                                .dataMassNames(dataMassNames)
                                .dimensionNames(dimensionNames)
                                .autoTagNames(autoTagNames)
                                .startDateTime(startDateTime)
                                .endDateTime(endDateTime)
                                .updateTime(now)
                                .build()
                );

            }
            return iapDataModels;

        }

        return null;
    }

    /**
     * @param iapDataModes     元数据
     * @param iapDataMetas     数据字典
     * @param importErrorInfos 错误信息
     * @return
     */
    public static List<ImportErrorInfo> checkErrorMsg(List<IapDataModel> iapDataModes, List<IapDataMeta> iapDataMetas, List<ImportErrorInfo> importErrorInfos) {
        Map<String, Map<String, Integer>> repeatCountMap = new HashMap();
        for (IapDataModel iapDataMode : iapDataModes) {
            String platformName = iapDataMode.getPlatformName();
            String enTableName = iapDataMode.getEnTableName();
            String cnTableName = iapDataMode.getCnTableName();
            Map<String, Integer> tableNamesMap = repeatCountMap.containsKey(platformName) ? repeatCountMap.get(platformName) : new HashMap<>();
            tableNamesMap.put(enTableName, tableNamesMap.containsKey(enTableName) ? tableNamesMap.get(enTableName) + 1 : 1);
            tableNamesMap.put(cnTableName, tableNamesMap.containsKey(cnTableName) ? tableNamesMap.get(cnTableName) + 1 : 1);
            repeatCountMap.put(platformName, tableNamesMap);

            for (IapDataMeta iapDataMeta : iapDataMetas) {
                if (iapDataMode.getEnTableName().equals(iapDataMeta.getModelEnTableName())) {
                    List<IapDataMeta> iapDataMetasFlag = iapDataMode.getIapDataMetas();
                    if (iapDataMetasFlag == null) {
                        iapDataMetasFlag = new ArrayList<>();
                    }
                    iapDataMetasFlag.add(iapDataMeta);
                    iapDataMode.setIapDataMetas(iapDataMetasFlag);
                }
            }
        }
        iapDataModes.forEach(iapDataModel -> {
            if (CollectionUtils.isEmpty(iapDataModel.getIapDataMetas())) {
                importErrorInfos.add(ImportErrorInfo.buildMessage("在数据字典中没有相关的" + iapDataModel.getEnTableName() + "表"));
            }
        });

        for (String platformName : repeatCountMap.keySet()) {
            Map<String, Integer> stringIntegerMap = repeatCountMap.get(platformName);
            for (String tableName : stringIntegerMap.keySet()) {
                if (stringIntegerMap.get(tableName) > 1) {
                    importErrorInfos.add(ImportErrorInfo.buildMessage(platformName + "系统中存在多个" + tableName + "表名"));
                }
            }
        }
        return importErrorInfos;
    }

    /**
     * 获取数据字典
     *
     * @param sheet
     * @param iapDataModes
     * @param importErrorInfos
     * @return
     */
    public static List<IapDataMeta> getIapDataMeta(Sheet sheet, List<IapDataModel> iapDataModes, List<ImportErrorInfo> importErrorInfos) {

        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum >= 0) {
            List<String> enTableNames = iapDataModes.stream().map(IapDataModel::getEnTableName).collect(Collectors.toList());
            List<IapDataMeta> iapDataMetas = new ArrayList<>();
            //表头所在列
            Map<String, Integer> headIndexMap = null;
            String enTableName = null;
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    //判断是否是表头
                    boolean dataMetaHead = isDataMetaHead(row);
                    if (dataMetaHead) {
                        enTableName = getEnTableName(row);
                        if (enTableNames.contains(enTableName)) {
                            headIndexMap = getHeadIndex(row, iapDataMetaHead);
                        } else {
                            importErrorInfos.add(ImportErrorInfo.buildMessage(enTableName + "不在元数据列表中"));
                            enTableName = null;
                        }

                    } else if (!StringUtils.isEmpty(enTableName)) {

                        boolean checkMetaHead = true;
                        for (String columnName : headIndexMap.keySet()) {
                            if (headIndexMap.get(columnName) == null) {
                                checkMetaHead = false;
                                importErrorInfos.add(ImportErrorInfo.buildMessage("数据字典" + enTableName + "表\"" + columnName + "\"列不存在"));
                            }
                        }
                        if (!checkMetaHead) {
                            continue;
                        }

                        String headName = "字段";
                        Integer cellIndex = headIndexMap.get(headName);
                        String fieldName = getStringValue(row, cellIndex);

                        String type = getStringValue(row, headIndexMap.get("类型"));
                        String defaultValue = getStringValue(row, headIndexMap.get("默认值"));
                        String comment = getStringValue(row, headIndexMap.get("说明"));
                        Boolean primaryKey = getBooleanValue(row, headIndexMap.get("是否主键"));
                        Boolean foreignKey = getBooleanValue(row, headIndexMap.get("是否外键"));
                        String fKComment = getStringValue(row, headIndexMap.get("外键说明"));
                        Boolean partitionKey = getBooleanValue(row, headIndexMap.get("是否分区字段"));
                        Boolean uniqueKey = getBooleanValue(row, headIndexMap.get("是否唯一约束"));
                        Boolean nullable = getBooleanValue(row, headIndexMap.get("是否允许空值"));

                        //空行
                        if (StringUtils.isEmpty(fieldName) && StringUtils.isEmpty(defaultValue) && StringUtils.isEmpty(comment) && primaryKey == null && foreignKey == null && StringUtils.isEmpty(fKComment) && partitionKey == null && uniqueKey == null && nullable == null) {
                            enTableName = null;
                            continue;
                        }

                        if (StringUtils.isEmpty(fieldName)) {
                            importErrorInfos.add(ImportErrorInfo.bankError(i + 1, cellIndex, headName));
                            continue;
                        }

                        iapDataMetas.add(
                                IapDataMeta
                                        .builder()
                                        .modelEnTableName(enTableName)
                                        .fieldName(fieldName)
                                        .type(type)
                                        .defaultValue(defaultValue)
                                        .comment(comment)
                                        .primaryKey(primaryKey)
                                        .foreignKey(foreignKey)
                                        .fKComment(fKComment)
                                        .partitionKey(partitionKey)
                                        .uniqueKey(uniqueKey)
                                        .nullable(nullable)
                                        .build());
                    }
                } else {
                    enTableName = null;
                }

            }
            return iapDataMetas;
        }
        return null;
    }

    /**
     * 获取通用表头
     *
     * @param headRow
     * @param heads
     * @return
     */
    public static Map<String, Integer> getHeadIndex(Row headRow, String[] heads) {
        Map<String, Integer> headIndex = new HashMap<>();
        for (int i = 0; i < heads.length; i++) {
            headIndex.put(heads[i], null);
        }
        short lastCellNum = headRow.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = headRow.getCell(i);
            if (cell != null) {
                cell.setCellType(CellType.STRING);
                String head = cell.getStringCellValue();
                head = matchHead(head, heads);
                if (head != null) {
                    headIndex.put(head, i);
                }
            }

        }
        return headIndex;
    }

    /**
     * 获取数据字典英文表名
     *
     * @param headRow
     * @return
     */
    public static String getEnTableName(Row headRow) {
        String enTableName = null;
        int rowNum = headRow.getRowNum();
        Sheet sheet = headRow.getSheet();
        Row enTableNameRow = sheet.getRow(rowNum - 1);
        short enTableNameLastCellNum = enTableNameRow.getLastCellNum();
        for (int j = 0; j < enTableNameLastCellNum; j++) {
            Cell enTableNameCell = enTableNameRow.getCell(j);
            if (enTableNameCell != null) {
                try {
                    String enTableNameCellValue = enTableNameCell.getStringCellValue();
                    enTableNameCellValue = matchEnTableName(enTableNameCellValue);
                    if (!StringUtils.isEmpty(enTableNameCellValue)) {
                        enTableName = enTableNameCellValue;
                        break;
                    }
                } catch (Exception e) {

                }
            }
        }
        return enTableName;
    }

    /**
     * 是否是元数据表头
     *
     * @param headRow
     * @return
     */
    public static boolean isDataMetaHead(Row headRow) {
        if (headRow == null) {
            return false;
        }
        short lastCellNum = headRow.getLastCellNum();
        int matchTimes = 0;
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = headRow.getCell(i);
            try {
                String stringCellValue = cell.getStringCellValue();
                String head = matchHead(stringCellValue, iapDataMetaHead);
                if (!StringUtils.isEmpty(head)) {
                    matchTimes++;
                }
            } catch (Exception e) {

            }
        }
        return matchTimes >= 3;
    }

    /**
     * 获取string内容
     *
     * @param headRow
     * @param columnIndex
     * @return
     */
    public static String getStringValue(Row headRow, int columnIndex) {
        Cell cell = headRow.getCell(columnIndex);
        if (cell != null) {
            cell.setCellType(CellType.STRING);
            String stringCellValue = cell.getStringCellValue();
            stringCellValue = stringCellValue == null ? null : stringCellValue.trim();
            return stringCellValue;
        }
        return null;

    }

    /**
     * 获取Data内容
     *
     * @param headRow
     * @param columnIndex
     * @return
     */
    public static Date getDataValue(Row headRow, int columnIndex) {
        Cell cell = headRow.getCell(columnIndex);
        if (cell != null) {
            return cell.getDateCellValue();
        }
        return null;
    }

    /**
     * 获取int内容
     *
     * @param headRow
     * @param columnIndex
     * @return
     */
    public static Integer getIntegerValue(Row headRow, int columnIndex) {
        try {
            Cell cell = headRow.getCell(columnIndex);
            Double numericCellValue = cell.getNumericCellValue();
            return Integer.valueOf(numericCellValue.intValue());
        } catch (Exception exception) {
            exception.printStackTrace();
        }
        return null;

    }

    /**
     * 获取boolean
     *
     * @param headRow
     * @param columnIndex
     * @return
     */
    public static Boolean getBooleanValue(Row headRow, int columnIndex) {
        try {
            Cell cell = headRow.getCell(columnIndex);
            String stringCellValue = cell.getStringCellValue();
            return StringUtils.isEmpty(stringCellValue) ? null : ("y".equals(stringCellValue.trim().toLowerCase()) || "是".equals(stringCellValue.trim()));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 正则获取表头
     *
     * @param head
     * @param iapHead
     * @return
     */
    public static String matchHead(String head, String[] iapHead) {
        head = head.trim();
        for (int i = 0; i < iapHead.length; i++) {
            String tempHead = iapHead[i];

            String regex = "^(" + tempHead + ")((\\(|（)(.*?))*$";
            Pattern pattern = Pattern.compile(regex);
            Matcher matcher = pattern.matcher(head);
            if (matcher.find()) {
                return matcher.group(1);
            }

        }
        return null;
    }

    /**
     * 匹配元数据英文表名
     *
     * @param head
     * @return
     */
    public static String matchEnTableName(String head) {
        if (!StringUtils.isEmpty(head)) {
            head = head.trim();
            String regex = "^(.+?)((\\(|（|\\s)(.*?))*$";
            Pattern pattern = Pattern.compile(regex);
            Matcher matcher = pattern.matcher(head);
            if (matcher.find()) {
                return matcher.group(1);
            }
        }
        return null;
    }

    /**
     * 读取Workbook
     *
     * @param file
     * @return
     */
    public static Workbook getWorkbook(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        InputStream is = null;
        try {
            is = file.getInputStream();
            if (fileName.endsWith(xls)) {
                //2003
                return new HSSFWorkbook(is);
            } else if (fileName.endsWith(xlsx)) {
                //2007
                return new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        throw new CustomParameterizedException("不是有效excel文件");

    }

}
